package com.wanda.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class ContractJDBC {
	
	public String getSendManager(String email) throws Exception{
		Connection conn = getConnection();
		Statement  stmt = conn.createStatement(); //3、Statement 接口需要通过Connection 接口进行实例化操作  
		String sql = "select wd_contract.*,wd_ht_build.build_name,wd_ht_unit.unit_code,wd_ht_room.room_code from wd_contract,wd_ht_build,wd_ht_unit,wd_ht_room ,wd_order where wd_contract.order_id = wd_order.order_id and wd_order.build_id=wd_ht_build.build_id and wd_order.unit_id = wd_ht_unit.unit_id and wd_order.room_id = wd_ht_room.room_id and wd_contract.sign_time between str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s')  and DATE_ADD(DATE_ADD(str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 1 DAY),INTERVAL -1 SECOND)  and wd_contract.user_id in(select user_id from wd_user where organization_id in(select organization_id from wd_user where email='"+email+"'))";	
		ResultSet  result = stmt.executeQuery(sql); //执行SQL 语句，查询数据库  
		String context="";
		int i =1;
		while (result.next()){  
			context+=(i++)+"."+result.getString("name")+"在"+result.getDate("sign_time")+"与"+result.getString("customer_name")+"签订"+result.getString("build_name")+"楼栋下 "+result.getString("unit_code")+"单元 "+result.getString("room_code")+" 号房<br>";
		}  
        result.close();  
        conn.close();  
        return context;
}  
	
	
	public List<String> getEmail(String sql) throws Exception{
		List<String> list = new ArrayList<String>();
		Connection conn = getConnection();
		Statement  stmt = conn.createStatement(); //3、Statement 接口需要通过Connection 接口进行实例化操作  
		ResultSet  result = stmt.executeQuery(sql); //执行SQL 语句，查询数据库  
		while (result.next()){  

	            System.out.println("email"+result.getString("email"));
	            list.add(result.getString("email"));  
		}  
        result.close();  
        conn.close(); // 4、关闭数据库 
        return list;
}  
	
	
    public Connection getConnection(){
    	Connection con = null;
		try {
			// 加载MySql的驱动类
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/wanda_java2";
			String username = "root";
			String password = "root";
			con =  DriverManager.getConnection(url , username , password ) ;   
		} catch (Exception e) {
			System.out.println("找不到驱动程序类 ，加载驱动失败！");
			e.printStackTrace();
		}
		return con;
		
	}
    public void coloConnection(Connection conn){
    	try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    }
}
